/*
 	This Procedure generats all the Running and Hold lots in Chiptrac System based on the Search criteria.
 	If Report Type is "ALL" then we will load Transit lots in addition to Running and Hold lots.
 
 	EXEC sp_wip_info_report 'iNVENTORY','0','0','0','0','0','iNVENTORY','', '0'
 	EXEC sp_wip_info_report 'fab option','0','0','0','0','0','all','','0'
 */
ALTER  PROC [dbo].[sp_wip_info_report] 

@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS	VARCHAR(200),
@VENDOR			VARCHAR(50),
@LOT_CATEGORY		VARCHAR(20),
@ASOFDATE		VARCHAR(50),
@STATUS			VARCHAR(20),
@REPORT_TYPE		VARCHAR(20),
@SEARCH_PART_NO		varchar(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN
	--Final Result table to store all the running lots Chiptrac based on the Search criteria
	CREATE TABLE #WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
 			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
 			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, 
 			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
 			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT, PS_TYPE VARCHAR(50),
 			PS_TYPE_NAME VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
 			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
 			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50) )
 
	CREATE TABLE #FG_RESULTS ( PROCESSSTEP_ID INT, FG_VENDOR VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
 			LOT_CATEGORY VARCHAR(50), QTY_IN INT,DATE_IN DATETIME,LOT_CURRENT_STATUS VARCHAR(50),
 			LOT_DATE_CODE VARCHAR(50),EXPIRY_DATE INT,lot_AGE INT,EXPECTED_DATE_OUT DATETIME, 
 			PART_NUMBER VARCHAR(50),PIN_COUNT VARCHAR(20),PART_STATUS VARCHAR(20),PACKAGE_TYPE VARCHAR(50),
 			ASSEMBLY_VENDOR VARCHAR(200),ASSEMBLY_PROCESSSTEP_ID INT)
 
	--Intermediate tables
	CREATE TABLE #STEP_IDS_TABLE  ( STEP_ID INT )
	CREATE TABLE #ASSEMBLY_PROCESSSTEP_TABLE  ( FGPROCESSSTEP_ID INT, ASSEMBLY_PROCESSSTEP_ID INT)
 
	/* 
 	In this table when no Process Type is selected we will load BaseProduct Ids and 
 	if Process Type is selected we we will loaad corresponding process Processstep_ids
	*/
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
 		IF @REPORT_TYPE = 'ALL' OR @REPORT_TYPE = 'WIP' OR @REPORT_TYPE = 'INVENTORY'
 		BEGIN
 			IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'WIP' OR @PROCESS_TYPE = 'INVENTORY' )
 			BEGIN
 				INSERT INTO #STEP_IDS_TABLE SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PROCESSSTEP_IDS ) ) 
 			END
 			ELSE
 			BEGIN
 				INSERT INTO #STEP_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PROCESSSTEP_IDS ) 
 			END
 		END
	END
 	--select * from #STEP_IDS_TABLE
 
	IF @ASOFDATE = '0'
	BEGIN
 		DECLARE @FAB_SQLSTRING VARCHAR(2000)
 		DECLARE @FAB_OPTION_SQLSTRING VARCHAR(2000) 
 		DECLARE @WS_SQLSTRING VARCHAR(2000)
		DECLARE @SORT_SQLSTRING VARCHAR(2000) 
 		DECLARE @DB_SQLSTRING VARCHAR(2000) 
 		DECLARE @ASSEMBLY_SQLSTRING VARCHAR(2000) 
 		DECLARE @TEST_SQLSTRING VARCHAR(2000)
 		DECLARE @CS_SQLSTRING VARCHAR(2000)
		DECLARE @MARK_SQLSTRING VARCHAR(2000)
 		DECLARE @FG_SQLSTRING VARCHAR(2000)
 		DECLARE @TRANSIT_STRING VARCHAR(4000)
 		DECLARE @FGTRANSIT_STRING VARCHAR(4000)
		DECLARE @BASE_PROCESS_TYPE VARCHAR(200)
	
	 	--HERE WE ARE RETREVNG FG DATA SEPARATELY THIS IS CALLED WHEN FG IS SELECTED SEPARATELY.
 		DECLARE @FG_REPORT	VARCHAR(2000) 
	
		IF ( @PROCESS_TYPE <> '0' AND @PROCESS_TYPE <> 'WIP' AND @PROCESS_TYPE <> 'INVENTORY' )
		BEGIN
			SELECT @BASE_PROCESS_TYPE = Base_Process_Type FROM Process_Types_Table WHERE Process_Type = @PROCESS_TYPE
		END		
 	
 		--IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'FT' OR @PROCESS_TYPE = 'FPO' OR @PROCESS_TYPE = 'TAPE & REEL' OR @PROCESS_TYPE = 'FG' )
		IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'WIP' AND @PROCESS_TYPE = 'INVENTORY' OR @BASE_PROCESS_TYPE = 'TEST' OR @BASE_PROCESS_TYPE = 'Class Store' OR @BASE_PROCESS_TYPE = 'Finished Goods' OR @BASE_PROCESS_TYPE = 'Mark & Pack' )
 		BEGIN
	 		DECLARE @FG_PROCESSSTEP_ID 		INT
 			DECLARE @ASSM_PROCESSSTEP_ID 	INT
 			DECLARE @PROCESSSTEP_STRING 	VARCHAR(2000)
 		
 			SET @PROCESSSTEP_STRING = N'DECLARE PROCESSSTEP_CURSOR CURSOR FOR 
 				SELECT DISTINCT LT.PROCESSSTEP_ID FROM LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
 				WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID '
 			
			IF @PROCESS_TYPE = '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE_Name = ''TEST'' OR PS_TYPE_NAME = ''Class Store'' OR PS_TYPE = ''Mark & Pack'' OR PS_TYPE = ''Finished Goods'') '
 			END
 			ELSE IF @PROCESS_TYPE = 'inventory'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE_Name = ''Finished Goods'' OR PS_TYPE_Name = ''TAPE & REEL'' OR PS_TYPE_Name = ''Class Store'' ) '
 			END
 			ELSE IF @PROCESS_TYPE = 'wip'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE_NAME = ''TEST'' or PS_TYPE_NAME = ''Mark & Pack'' ) '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'FT'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''TEST'' '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'Class Store'
 			BEGIN 
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''Class Store'' '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'Mark & Pack'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''Mark & Pack'' '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'Finished Goods'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''Finished Goods'' '
 			END
 
 			IF @PROCESSSTEP_IDS <> '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 			END
 
 			IF @VENDOR <> '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
 			END
 
 			IF @LOT_CATEGORY <> '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 			END
 
 			IF @STATUS = '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
 			END
 			ELSE
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 			END
 
 			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 			END
 
 			SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' UNION
  			SELECT DISTINCT LT.PROCESSSTEP_ID 
 			FROM SHIPPED_LOT SL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
 			WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
 			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND STATUS !=''CLOSE'' '
 			
 			IF @PROCESS_TYPE = '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE_Name = ''TEST'' OR PS_TYPE_NAME = ''Class Store'' OR PS_TYPE = ''Mark & Pack'' OR PS_TYPE = ''Finished Goods'') '
 			END
 			ELSE IF @PROCESS_TYPE = 'inventory'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE_Name = ''Finished Goods'' OR PS_TYPE_Name = ''TAPE & REEL'' OR PS_TYPE_Name = ''Class Store'' ) '
 			END
 			ELSE IF @PROCESS_TYPE = 'wip'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE_NAME = ''TEST'' or PS_TYPE_NAME = ''Mark & Pack'' ) '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'FT'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''TEST'' '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'Class Store'
 			BEGIN 
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''Class Store'' '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'Mark & Pack'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''Mark & Pack'' '
 			END
 			ELSE IF @BASE_PROCESS_TYPE = 'Finished Goods'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE_Name = ''Finished Goods'' '
 			END

 			IF @PROCESSSTEP_IDS <> '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 			END
 
 			IF @VENDOR <> '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
 			END
 
 			IF @LOT_CATEGORY <> '0'
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 			END
 
 			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 			BEGIN
 				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 			END
 
 			--PRINT '@PROCESSSTEP_STRING'+@PROCESSSTEP_STRING
 			EXEC (@PROCESSSTEP_STRING)
 
 			OPEN PROCESSSTEP_CURSOR 
 			FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @FG_PROCESSSTEP_ID
 			WHILE @@FETCH_STATUS = 0
 			BEGIN
 				SELECT @ASSM_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID ) WHERE PS_TYPE = 'ASSEMBLY'
 				
 				INSERT INTO #ASSEMBLY_PROCESSSTEP_TABLE (FGPROCESSSTEP_ID,ASSEMBLY_PROCESSSTEP_ID) VALUES (@FG_PROCESSSTEP_ID,@ASSM_PROCESSSTEP_ID)
 			
				SET @FG_PROCESSSTEP_ID = NULL
				SET @ASSM_PROCESSSTEP_ID = NULL
 			
 				FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @FG_PROCESSSTEP_ID
 			END
 				 																
 			CLOSE PROCESSSTEP_CURSOR
 			DEALLOCATE PROCESSSTEP_CURSOR 
 		END
 	
 		-- Getting the Gross Die for Die Bank Processstep Ids.
 		-- here we are considering all die bank parts as there will be not die bank lots in lot table.
 		IF @PROCESS_TYPE = '0' OR @BASE_PROCESS_TYPE = 'DIE BANK' OR @PROCESS_TYPE = 'INVENTORY'
 		BEGIN	
 			DECLARE @DB_PROCESSSTEP_ID 		INT
 			DECLARE @FAB_PROCESSSTEP_ID 	INT
 			DECLARE @FABPROCESSSTRING 	VARCHAR(1000)
 		
	 		CREATE TABLE #FAB_PROCESSSTEP_TABLE  ( DBPROCESSSTEP_ID INT, FAB_PROCESSSTEP_ID INT )		
 		
 			SET @FABPROCESSSTRING = N'
 			DECLARE PROCESSSTEP_CURSOR CURSOR FOR 
 			SELECT DISTINCT LT.PROCESSSTEP_ID FROM LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS WHERE 
 			LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = ''Die Bank'' '
 
			IF @PROCESSSTEP_IDS <> '0'
 			BEGIN
 				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND p.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 			END
 
 			IF @VENDOR <> '0'
 			BEGIN
 				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
 			END
 
 			IF @LOT_CATEGORY <> '0'
 			BEGIN
 				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 			END
 
 			IF @STATUS = '0'
 			BEGIN
 				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
 			END
 			ELSE
 			BEGIN
 				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 			END
 
 			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 			BEGIN
 				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 			END
 
 			EXEC (@FABPROCESSSTRING)
 
 			OPEN PROCESSSTEP_CURSOR 
 			FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @DB_PROCESSSTEP_ID
 			WHILE @@FETCH_STATUS = 0
 			BEGIN
 				--PRINT '@DB_PROCESSSTEP_ID'+CONVERT(VARCHAR,@DB_PROCESSSTEP_ID)
 				SELECT @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @DB_PROCESSSTEP_ID ) WHERE PS_TYPE = 'FAB'
 				
 				INSERT INTO #FAB_PROCESSSTEP_TABLE (DBPROCESSSTEP_ID,FAB_PROCESSSTEP_ID) VALUES (@DB_PROCESSSTEP_ID,@FAB_PROCESSSTEP_ID)
 				SET @DB_PROCESSSTEP_ID = NULL
 				SET @FAB_PROCESSSTEP_ID = NULL
 			
 				FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @DB_PROCESSSTEP_ID
 			END
 				 																
 			CLOSE PROCESSSTEP_CURSOR
 			DEALLOCATE PROCESSSTEP_CURSOR 
		END
 	
 		--Inserting Fab Data
 		--SELECT * FROM vw_Fab_Wip_View
 		SET @FAB_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
 		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,	LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, STAGE_NAME, 
 		STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,GROSS_DIE, OUTLOOK_DATE, WAFER_ID, PART_NUMBER, 
		BASE_PRODUCT, BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID ) 
 		SELECT  PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, STAGE_NAME, 
 		STAGE_POSITION, LOT_CURRENT_STATUS, FL_EXPECTED_DATE_OUT,GROSS_DIE_WAFER, FL_OUTLOOK_DATE, WAFER_ID, PART_NUMBER, 
		BASE_PRODUCT, BASE_PRODUCT_ID, PS_TYPE, PS_Type_Name, PS_TYPE_ID
 		FROM vw_Fab_Wip_View  WHERE 1=1 '
	 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END	
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
 
 		--Insertig Wafer Store Data	
		-- SELECT * FROM vw_WaferStore_Wip_View
 		SET @WS_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,	LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
 		GROSS_DIE, WAFER_ID, PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID ) 
 		SELECT  PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, 
		LOT_CURRENT_STATUS, GROSS_DIE_WAFER,WAFER_ID,PART_NUMBER, BASE_PRODUCT, 
 		BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID FROM vw_WaferStore_Wip_View WHERE 1=1 '
	 
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END	
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END

 		--Inserting SORT Data
 		--SELECT * FROM vw_SORT_Wip_View
 		SET @SORT_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
 		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,	LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, STAGE_NAME, 
 		STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,GROSS_DIE, PART_NUMBER, 
		BASE_PRODUCT, BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID ) 
 		SELECT PROCESSSTEP_ID, Location, LOT_NUMBER, FAB_LOT_NUMBER, LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, STAGE_NAME, 
 		STAGE_POSITION, LOT_CURRENT_STATUS, SL_EXPECTED_DATE_OUT,GROSS_DIE_WAFER, PART_NUMBER, 
		BASE_PRODUCT, BASE_PRODUCT_ID, PS_TYPE, PS_Type_Name, PS_TYPE_ID
 		FROM vw_SORT_Wip_View  WHERE 1=1 '
	 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND Location = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END	
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
	 
 		-- Inserting DIE BANK Data
 		SET @DB_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,	LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
 			WAFER_ID,PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID,GROSS_DIE ) 
 		SELECT  VDB.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER, LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
 		WAFER_ID, PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#FAB_PROCESSSTEP_TABLE FPT  WHERE PARAMETER_KEY = ''Gross Die/Wafer'' 
		AND PP.PROCESSSTEP_ID = FPT.FAB_PROCESSSTEP_ID AND DBPROCESSSTEP_ID = VDB.PROCESSSTEP_ID ) AS GROSS_DIE
 		FROM vw_DieBank_Wip_View VDB WHERE 1=1 '
	 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
 
 		--Inserting Assembly Data
 		SET @ASSEMBLY_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
 			( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 			LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
 			STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,
 			THINKNESS, GRIND, PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
 			PS_Type, PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE ) 
 		SELECT  
 			VA.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, ISNULL( FAB_LOT_NUMBER, LOT_NUMBER),
 			LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
 			STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT, 
 			THICKNESS, GRIND,PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
 			PS_TYPE, PS_Type_Name,PS_TYPE_ID,
 			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = VA.PROCESSSTEP_ID ) AS PACKAGE_TYPE
 		FROM 
 			vw_Assembly_Wip_View VA WHERE 1=1 '
 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
 
	 	--Inserting Test FPO Data
 		SET @TEST_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
 			( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 			LOT_CATEGORY, QTY_IN, DATE_IN, STAGE_NAME, 
 			STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,
 			PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
 			PS_Type, PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE ) 
 		SELECT  
 			VTEST.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, ISNULL( FAB_LOT_NUMBER, LOT_NUMBER),
 			LOT_CATEGORY, QTY_IN, DATE_IN, STAGE_NAME, 
 			STAGE_POSITION, LOT_CURRENT_STATUS, FTL_EXPECTED_DATE, 
 			PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
 			PS_TYPE, PS_Type_Name,PS_TYPE_ID,
 			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VTEST.PROCESSSTEP_ID ) AS PACKAGE_TYPE
 		FROM 
 			vw_Test_Wip_View VTEST WHERE 1 = 1 '
	 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @TEST_SQLSTRING = @TEST_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @TEST_SQLSTRING = @TEST_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @TEST_SQLSTRING = @TEST_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @TEST_SQLSTRING = @TEST_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @TEST_SQLSTRING = @TEST_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
 
 		--Inserting ClassStore Data
 		--SELECT * FROM vw_ClassStore_Wip_View
 		SET @CS_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
 			( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 			LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
 			PART_NUMBER, BASE_PRODUCT,BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE ) 
 		SELECT  
 			VCS.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,
 			LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
 			PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
 			PS_TYPE_NAME,PS_TYPE_ID,
 			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VCS.PROCESSSTEP_ID ) AS PACKAGE_TYPE
 		FROM 
 			vw_ClassStore_Wip_View VCS WHERE 1=1 '
	 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @CS_SQLSTRING = @CS_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @CS_SQLSTRING = @CS_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @CS_SQLSTRING = @CS_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @CS_SQLSTRING = @CS_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @CS_SQLSTRING = @CS_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
	 
 		--Inserting FG Data
 		SET @FG_SQLSTRING = N'
 		INSERT INTO #WIP_RESULTS 
 			( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 			LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS,EXPECTED_DATE_OUT,
 			LABLE_NAME,PART_NUMBER,BASE_PRODUCT,BASE_PRODUCT_ID,PS_Type, PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE ) 
	 
 		SELECT  
 			VFG.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,
 			LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS,EXPECTED_DATE_OUT,
 			LABLE_NAME,PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
 			PS_Type, PS_TYPE_NAME,PS_TYPE_ID,
 			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VFG.PROCESSSTEP_ID ) AS PACKAGE_TYPE
 		FROM 
 			vw_FG_Wip_View VFG WHERE 1=1 '
	 	
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		IF @STATUS <> '0'
 		BEGIN
 			SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
  
 		--Inserting FG Data FOR FG REPORT.
 		-- FOR GETTING ASSEMBLY VENDOR FOR THE FG LOT, FIRST CURSOR GETS THE ASSEMBLY PROCESSSTEP_IDS FOR DISTINCT FG PROCESSSTEP_IDS
 		IF @PROCESS_TYPE = 'FG'
	 	BEGIN
 			SET @FG_REPORT = N'INSERT INTO #FG_RESULTS
 			SELECT LT.PROCESSSTEP_ID,L_VENDOR_NAME AS FG_VENDOR, LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,QTY_IN,DATE_IN,
 			(CASE WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''	ELSE ''HOLD'' END) AS LOT_CURRENT_STATUS,
 			LOT_DATE_CODE,DATEDIFF( DD,DATE_IN,ISNULL( IL.EXPECTED_DATE_OUT,CURRENT_TIMESTAMP ) )AS EXPIRY_DATE,DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) AS lot_AGE,
 			IL.EXPECTED_DATE_OUT as EXPECTED_DATE_OUT ,PART_NUMBER,	(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PIN'' AND PROCESSSTEP_ID = PS.PROCESSSTEP_ID ) AS PIN_COUNT,
 			P.PART_STATUS,	(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID ) AS PACKAGE_TYPE,
 			--(SELECT L_VENDOR_NAME FROM LOT_TABLE LT1,LOCATION L1 WHERE LT1.LOCATION_ID = L1.LOCATION_ID AND LT1.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND LT1.FAB_LOT_NUMBER = LT.FAB_LOT_NUMBER AND LT1.LOT_NUMBER = LT.LOT_NUMBER ) --AND FGPROCESSSTEP_ID = LT.PROCESSSTEP_ID) 
 			NULL AS ASSEMBLY_VENDOR,APT.ASSEMBLY_PROCESSSTEP_ID	FROM 
 			INVENTORY_LOT IL,LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,#ASSEMBLY_PROCESSSTEP_TABLE APT,LOCATION L
 			WHERE IL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
 			AND PS.PROCESSSTEP_ID = APT.FGPROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID	AND PS_TYPE = ''FG'' '
 	
 			IF @PROCESSSTEP_IDS <> '0'
 			BEGIN
 				SET @FG_REPORT = @FG_REPORT + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 			END
 			IF @VENDOR <> '0'
 			BEGIN
	 			SET @FG_REPORT = @FG_REPORT + ' AND L.L_VENDOR_NAME = '''+ @VENDOR +''''
 			END
 			IF @LOT_CATEGORY <> '0'
 			BEGIN
 				SET @FG_REPORT = @FG_REPORT + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 			END
 			IF @STATUS = '0'
 			BEGIN
	 			SET @FG_REPORT = @FG_REPORT + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
 			END
 			ELSE
 			BEGIN
	 			SET @FG_REPORT = @FG_REPORT + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 			END
 			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 			BEGIN
 				SET @FG_REPORT = @FG_REPORT + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 			END
 			--PRINT ('@FG_REPORT ----'+@FG_REPORT)
	 		
			SET @FGTRANSIT_STRING = N'INSERT INTO #FG_RESULTS
 			SELECT LT.PROCESSSTEP_ID,L_VENDOR_NAME AS FG_VENDOR, LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,SHIP_QTY,SHIP_DATE,
 			STATUS,	LOT_DATE_CODE,NULL AS EXPIRY_DATE,DATEDIFF( DD,SHIP_DATE,CURRENT_TIMESTAMP ) AS lot_AGE,
 			NULL as EXPECTED_DATE_OUT ,PART_NUMBER,	(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PIN'' AND PROCESSSTEP_ID = PS.PROCESSSTEP_ID ) AS PIN_COUNT,
 			P.PART_STATUS, (SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID ) AS PACKAGE_TYPE,
 			NULL AS ASSEMBLY_VENDOR,APT.ASSEMBLY_PROCESSSTEP_ID	FROM 
 			SHIPPED_LOT SL,LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,#ASSEMBLY_PROCESSSTEP_TABLE APT,LOCATION L
 			WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
 			AND PS.PROCESSSTEP_ID = APT.FGPROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID	AND SL.STATUS = ''TRANSIT''
 			AND PS_TYPE = ''FG'' '

 			IF @PROCESSSTEP_IDS <> '0'
 			BEGIN
 				SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 			END
 			IF @VENDOR <> '0'
 			BEGIN
 				SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND L.L_VENDOR_NAME = '''+ @VENDOR +''''
 			END
 			IF @LOT_CATEGORY <> '0'
 			BEGIN
	 			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 			END
 			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 			BEGIN
 				SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 			END
 			--PRINT ('@FG_REPORT ----'+@FG_REPORT)
	 	END
 	
 		--Inserting Transit Data 
 		SET @TRANSIT_STRING = N'
 		INSERT INTO #WIP_RESULTS 
 		( DATE_IN,QTY_IN,WAFER_QTY_IN,LOT_CURRENT_STATUS,WAFER_ID,PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 		LOT_CATEGORY,PART_NUMBER,BASE_PRODUCT,BASE_PRODUCT_ID, PS_Type, PS_TYPE_NAME,PS_TYPE_ID, STAGE_NAME, PACKAGE_TYPE) 
	 	SELECT ship_date,
 		(CASE WHEN (PS_TYPE != ''FAB'' AND PS_TYPE != ''FAB OPTION'' AND PS_TYPE != ''WAFER BANK'' AND PS_TYPE != ''SORT'' ) THEN SHIP_QTY ELSE NULL END ), 
 		(CASE WHEN (PS_TYPE = ''FAB'' OR PS_TYPE = ''FAB OPTION'' OR PS_TYPE = ''WAFER BANK'' OR PS_TYPE = ''SORT'' ) THEN SHIP_QTY ELSE NULL END ),
 		status,WAFER_ID,LT.processstep_id,(SELECT L_Vendor_Name FROM Location WHERE Location_ID = Lt.Location_Id ),
 		lot_number,fab_lot_number,lt.lot_category,part_number,
 		( SELECT Part_Number FROM PRODUCT WHERE Product_Id = ( SELECT P_Parent_Product_Id FROM  Product WHERE ProcessStep_Id = P.ProcessStep_ID )),
 		P_Parent_Product_Id,ps_type, PS_TYPE_NAME,
 		(CASE WHEN PS_TYPE_NAME = ''FAB'' THEN 1
 			WHEN PS_TYPE_NAME = ''WAFER STORE'' THEN 2
 			WHEN PS_TYPE_NAME = ''SORT'' THEN 3
 			WHEN PS_TYPE_NAME = ''DIE BANK'' THEN 4
 			WHEN PS_TYPE_NAME = ''ASSEMBLY'' THEN 5
 			WHEN PS_TYPE_NAME = ''TEST'' THEN 6
 			WHEN PS_TYPE_NAME = ''CLASS STORE'' THEN 7
 			WHEN PS_TYPE_NAME = ''MARK & PACK'' THEN 8
 			WHEN PS_TYPE_NAME = ''FG'' THEN 9
 			ELSE NULL END),''Transit'',
 		( case 
 			when ps_type_Name = ''Assembly'' then ( SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER PP  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = lt.PROCESSSTEP_ID )
 			when ( ps_type_name = ''Test'' or ps_type_name = ''Class Store'' or ps_type_name = ''mark & pack'' or ps_type_name = ''Finished Goods'' ) then 
 				( SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = lt.PROCESSSTEP_ID )
 			else null end )
 		FROM SHIPPED_LOT SL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
 		WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
 		AND SL.STATUS <> ''CLOSE'' ' 	
 		IF @PROCESS_TYPE <> '0'
 		BEGIN
 			SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND PS.PS_TYPE = '''+ @PROCESS_TYPE+''''
 		END
 		IF @PROCESSSTEP_IDS <> '0'
 		BEGIN
 			SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND lt.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
 		END
 		IF @VENDOR <> '0'
 		BEGIN
 			SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''')'
 		END
 		IF @LOT_CATEGORY <> '0'
 		BEGIN
 			SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
 		END
 		/*IF @STATUS <> '0'
 		BEGIN
 			SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
 		END*/
 		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
 		BEGIN
 			SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
 		END
		
		-- Query Execution starts here
		IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = '0' )
 		BEGIN
			--PRINT 'Execute all  ' + @FG_SQLSTRING
 			EXEC( @FAB_SQLSTRING + @WS_SQLSTRING + @SORT_SQLSTRING + @DB_SQLSTRING + @ASSEMBLY_SQLSTRING + @TEST_SQLSTRING + @CS_SQLSTRING + @MARK_SQLSTRING + @FG_SQLSTRING )
 		END
 		ELSE IF ( @REPORT_TYPE = 'WIP' AND @PROCESS_TYPE = 'WIP' )
 		BEGIN
 			EXEC(@FAB_SQLSTRING + @ASSEMBLY_SQLSTRING + @TEST_SQLSTRING + @SORT_SQLSTRING + @MARK_SQLSTRING )
 		END
 		ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'INVENTORY' )
 		BEGIN
 			EXEC( @WS_SQLSTRING +  @DB_SQLSTRING + @CS_SQLSTRING + @FG_SQLSTRING )
 		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'FAB' )
		BEGIN
			EXEC ( @FAB_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'WAFER STORE' )
		BEGIN
			EXEC ( @WS_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'SORT' )
		BEGIN
			EXEC ( @SORT_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'DIE BANK' )
		BEGIN
			EXEC ( @DB_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'ASSEMBLY' )
		BEGIN
			EXEC ( @ASSEMBLY_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'TEST' )
		BEGIN
			EXEC ( @TEST_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'Class Store' )
		BEGIN
			EXEC ( @CS_SQLSTRING )
		END
		ELSE IF ( @BASE_PROCESS_TYPE = 'Mark & Pack' )
		BEGIN
			EXEC( @MARK_SQLSTRING )
		END
 		ELSE IF ( ( @REPORT_TYPE = 'FINISHED' OR  @REPORT_TYPE = 'INVENTORY' or @REPORT_TYPE = 'all' ) AND @PROCESS_TYPE = 'FG'  )
 		BEGIN
 			IF @REPORT_TYPE = 'ALL'
 			BEGIN
 				EXEC(@FG_REPORT+@FGTRANSIT_STRING)
 			END
 			ELSE
 			BEGIN
 				EXEC(@FG_REPORT)
 			END
	 
 			DECLARE @FG_cursor CURSOR
 			DECLARE @ASSM_cursor CURSOR
	 
 			DECLARE @FG_STEP_ID INT
 			DECLARE @ASSM_STEP_ID INT
 			DECLARE @ASSM_VENDOR VARCHAR(50)
 			DECLARE @ASSM_VENDORS VARCHAR(200)
 			DECLARE @LOT_NUMBER VARCHAR(50)
 			DECLARE @FAB_LOT_NUMBER VARCHAR(50)
 
	 		SET @FG_cursor = CURSOR FOR SELECT PROCESSSTEP_ID,FAB_LOT_NUMBER,LOT_NUMBER,ASSEMBLY_PROCESSSTEP_ID FROM #FG_RESULTS 
 			OPEN @FG_cursor
 			FETCH NEXT FROM @FG_cursor INTO @FG_STEP_ID,@FAB_LOT_NUMBER,@LOT_NUMBER,@ASSM_STEP_ID
 			WHILE @@FETCH_STATUS = 0
 			BEGIN	
	 			SET @ASSM_cursor = CURSOR FOR SELECT distinct L_VENDOR_NAME 
 				FROM LOT_TABLE LT1,LOCATION L1 WHERE LT1.LOCATION_ID = L1.LOCATION_ID 
 				AND LT1.PROCESSSTEP_ID = @ASSM_STEP_ID AND LT1.fab_LOT_NUMBER = @FAB_LOT_NUMBER 
 				OPEN @ASSM_cursor 
	 			FETCH NEXT FROM @ASSM_cursor INTO @ASSM_VENDOR
 				WHILE @@FETCH_STATUS = 0
 				BEGIN	
 					IF ( @ASSM_VENDORS IS NULL )
 					BEGIN
 						SET @ASSM_VENDORS = @ASSM_VENDOR  
 					END
 					ELSE
 					BEGIN
	 					SET @ASSM_VENDORS = @ASSM_VENDORS +','+ @ASSM_VENDOR  
 					END
 					FETCH NEXT FROM @ASSM_cursor INTO @ASSM_VENDOR
 				END
 				CLOSE @ASSM_cursor 
 				DEALLOCATE @ASSM_cursor 
	 			UPDATE #FG_RESULTS SET ASSEMBLY_VENDOR = @ASSM_VENDORS WHERE PROCESSSTEP_ID = @FG_STEP_ID AND FAB_LOT_NUMBER = @FAB_LOT_NUMBER
 
 				SET @ASSM_VENDORS = NULL
 				SET @ASSM_VENDOR  = NULL
 
 				FETCH NEXT FROM @FG_cursor INTO @FG_STEP_ID,@FAB_LOT_NUMBER,@LOT_NUMBER,@ASSM_STEP_ID
 			END
 
	 		CLOSE @FG_cursor
 			DEALLOCATE @FG_cursor
		END

		IF( @REPORT_TYPE = 'ALL'  )
		BEGIN
			EXEC ( @TRANSIT_STRING ) 
		END

 		--Final Result Table retreving resultant data.
		IF @PROCESS_TYPE <> 'FG'
 		BEGIN
			IF @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'INVENTORY' OR  @PROCESS_TYPE = 'WIP'
			BEGIN
	 			SELECT PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 				LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
 				STAGE_POSITION, (CASE WHEN LOT_CURRENT_STATUS = 'Running' THEN 'ACTIVE'	ELSE UPPER(LOT_CURRENT_STATUS) END) AS LOT_CURRENT_STATUS, 
 				EXPECTED_DATE_OUT, DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP )AS AGE, OUTLOOK_DATE, GROSS_DIE, WAFER_ID, PART_NUMBER, 
				BASE_PRODUCT, BASE_PRODUCT_ID, PS_TYPE, PS_TYPE_ID, THINKNESS, GRIND, SL_FLOW, SL_TEST_PROGRAM, SL_HOLD_DATE, SL_TESTER, MASK, LABLE_NAME, PACKAGE_TYPE
 				FROM #WIP_RESULTS ORDER BY BASE_PRODUCT,PS_TYPE_ID,PART_NUMBER,STAGE_POSITION,PACKAGE_TYPE
			END
			ELSE
			BEGIN
	 			SELECT PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
 				LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
 				STAGE_POSITION, (CASE WHEN LOT_CURRENT_STATUS = 'Running' THEN 'ACTIVE'	ELSE UPPER(LOT_CURRENT_STATUS) END) AS LOT_CURRENT_STATUS, 
 				EXPECTED_DATE_OUT, DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP )AS AGE, OUTLOOK_DATE, GROSS_DIE, WAFER_ID, PART_NUMBER, 
				BASE_PRODUCT, BASE_PRODUCT_ID, PS_TYPE, PS_TYPE_ID, THINKNESS, GRIND, SL_FLOW, SL_TEST_PROGRAM, SL_HOLD_DATE, SL_TESTER, MASK, LABLE_NAME, PACKAGE_TYPE
 				FROM #WIP_RESULTS WHERE PS_TYPE  = @PROCESS_TYPE ORDER BY BASE_PRODUCT,PS_TYPE_ID,PART_NUMBER,STAGE_POSITION,PACKAGE_TYPE
			END
 		END	
		ELSE
		BEGIN
 			SELECT 	PROCESSSTEP_ID,FG_VENDOR, LOT_NUMBER, FAB_LOT_NUMBER ,
 			LOT_CATEGORY , QTY_IN ,DATE_IN ,LOT_CURRENT_STATUS ,
 			LOT_DATE_CODE ,EXPIRY_DATE ,lot_AGE ,EXPECTED_DATE_OUT , 
 			PART_NUMBER ,PIN_COUNT ,PART_STATUS ,PACKAGE_TYPE ,
 			ASSEMBLY_VENDOR ,ASSEMBLY_PROCESSSTEP_ID 
 			from #FG_RESULTS 
		END
	END
	ELSE	
	BEGIN
		exec sp_wip_info_as_of_date @PROCESS_TYPE,@PROCESSSTEP_IDS,@VENDOR,@LOT_CATEGORY,@ASOFDATE,@STATUS,@REPORT_TYPE,@SEARCH_PART_NO
	END
END
